import sqlite3 from 'sqlite3'
import { app } from 'electron'
import path from 'path'

// 开启SQLite日志
sqlite3.verbose()

// 可选：设置更详细的日志
// process.env.SQLITE_DEBUG = '1'

export function initializeDatabase(defPath = null) {
  if (defPath == null) {
    defPath = path.join(app.getPath('userData'), 'electron-vite.db')
  }

  console.log('数据库路径:', defPath)
  const db = new sqlite3.Database(defPath, (err) => {
    if (err) {
      console.error('Database opening error:', err)
    } else {
      createTables(db)
    }
  })
  return db
}

function createTables(db) {
  db.run(
    `CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,pwd TEXT,shop_name TEXT,login integer,cookie TEXT,created_at TIMESTAMP,yfmb TEXT,zdyfmb TEXT);`,
    (err) => {
      if (err) console.error('Table creation error:', err)
    }
  )

  // 创建配置表
  db.run(
    `CREATE TABLE IF NOT EXISTS configs (id INTEGER PRIMARY KEY AUTOINCREMENT,key TEXT UNIQUE,value TEXT,created_at TIMESTAMP,updated_at TIMESTAMP);`,
    (err) => {
      if (err) console.error('Table creation error:', err)
    }
  )
  db.run(
    `CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,task TEXT,result TEXT,created_at TIMESTAMP,shops TEXT,mbs TEXT);`,
    (err) => {
      if (err) console.error('Table creation error:', err)
    }
  )
  db.run(
    `CREATE TABLE IF NOT EXISTS mbs (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,content TEXT,zt integer,created_at TIMESTAMP,shops TEXT,mbs TEXT);`,
    (err) => {
      if (err) console.error('Table creation error:', err)
    }
  )
  db.run(
    `CREATE TABLE IF NOT EXISTS user_task (id INTEGER PRIMARY KEY AUTOINCREMENT,created_at TIMESTAMP,good TEXT,result uid,zt integer,uid TEXT,mb TEXT,params TEXT,task_id TEXT,resp TEXT,deal_params TEXT);`,
    (err) => {
      if (err) console.error('Table creation error:', err)
    }
  )
}

// 获取带有时区的当前时间
function getCurrentDateTime() {
  const now = new Date()
  // 获取当前时间，并添加8小时（中国标准时间）
  now.setHours(now.getHours())

  const year = now.getFullYear()
  const month = String(now.getMonth() + 1).padStart(2, '0')
  const day = String(now.getDate()).padStart(2, '0')
  const hours = String(now.getHours()).padStart(2, '0')
  const minutes = String(now.getMinutes()).padStart(2, '0')
  const seconds = String(now.getSeconds()).padStart(2, '0')

  return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`
}

export function addUser(db, name, pwd) {
  console.log('addUser:', name, pwd)
  return new Promise((resolve, reject) => {
    const createdAt = getCurrentDateTime()
    db.run(
      'INSERT INTO users (name, pwd, created_at,login) VALUES (?, ?, ?,?)',
      [name, pwd, createdAt, 0],
      function (err) {
        if (err) {
          console.error('Error inserting user:', err)
          reject(err)
        } else resolve(this.lastID)
      }
    )
  })
}

export function clearLogin(db) {
  return new Promise((resolve, reject) => {
    db.run('update users set login = 0 ,cookie=""', [], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}

export function updateUserYfmbByUsername(db, id, yfmb) {
  console.log('updateUserYfmbByUsername:', id, yfmb)
  return new Promise((resolve, reject) => {
    db.run('UPDATE users SET zdyfmb = ? WHERE id = ?', [yfmb, id], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}
/**
 * 设置用户自定义模板
 */
export function settingUserYfmbByUsername(db, name, zdyfmb) {
  return new Promise((resolve, reject) => {
    db.run('UPDATE users SET zdyfmb = ? WHERE name = ?', [zdyfmb, name], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}

export function addMb(db, content, zt) {
  return new Promise((resolve, reject) => {
    const createdAt = getCurrentDateTime()
    db.run(
      'INSERT INTO mbs (content, zt, created_at) VALUES (?, ?, ?)',
      [content, zt, createdAt],
      function (err) {
        if (err) reject(err)
        else resolve(this.lastID)
      }
    )
  })
}

export function findUserByName(db, name) {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM users WHERE name = ?', [name], function (err, rows) {
      if (err) {
        reject(err)
      } else {
        resolve(rows)
      }
    })
  })
}
// 查询已登录的用户
export function findUserByLogin(db) {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM users WHERE login = 1', [], function (err, rows) {
      if (err) {
        reject(err)
      } else {
        resolve(rows)
      }
    })
  })
}
export function findAllMbs(db) {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM mbs WHERE zt = 0', [], function (err, rows) {
      if (err) {
        reject(err)
      } else {
        resolve(rows)
      }
    })
  })
}
// 查询用户，返回用户所有信息
export function findUserAll(db, name) {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM users WHERE name = ?', [name], function (err, rows) {
      if (err) {
        reject(err)
      } else {
        resolve(rows)
      }
    })
  })
}

export function loginUser(db, name, cookie) {
  return new Promise((resolve, reject) => {
    // 更新用户的cookie、店铺名称和登录状态
    db.run('UPDATE users SET cookie=?, login=1 WHERE name=?', [cookie, name], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}

export function editUser(db, name, pwd, id) {
  return new Promise((resolve, reject) => {
    // 更新用户的cookie、店铺名称和登录状态
    db.run('UPDATE users SET name=?, pwd=? WHERE id=?', [name, pwd, id], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}

export function setShopName(db, name, shop_name) {
  return new Promise((resolve, reject) => {
    // 更新用户的cookie、店铺名称和登录状态
    db.run('UPDATE users SET shop_name=? WHERE name=?', [shop_name, name], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}

export function loginOut(db, name) {
  return new Promise((resolve, reject) => {
    // 更新用户的cookie、店铺名称和登录状态
    db.run('UPDATE users SET login=0 WHERE name=?', [name], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}

export function getUsers(db, { page = 1, pageSize = 10, keyword = '' } = {}) {
  return new Promise((resolve, reject) => {
    // 计算偏移量
    const offset = (page - 1) * pageSize

    // 构建查询条件
    let query = 'SELECT * FROM users'
    let countQuery = 'SELECT COUNT(*) AS total FROM users'
    const params = []

    if (keyword) {
      query += ' WHERE name LIKE ?'
      countQuery += ' WHERE name LIKE ?'
      const likeKeyword = `%${keyword}%`
      params.push(likeKeyword)
    }

    // 添加分页
    query += ' LIMIT ? OFFSET ?'
    params.push(pageSize, offset)

    // 先查询用户列表
    db.all(query, params, (err, rows) => {
      if (err) {
        reject(err)
        return
      }

      // 再查询总记录数
      db.get(countQuery, keyword ? [`%${keyword}%`] : [], (countErr, countRow) => {
        if (countErr) {
          reject(countErr)
          return
        }

        resolve({
          users: rows,
          total: countRow.total,
          page,
          pageSize,
          totalPages: Math.ceil(countRow.total / pageSize)
        })
      })
    })
  })
}
// 模板
export function addTemp(db, name, str) {
  return new Promise((resolve, reject) => {
    const createdAt = getCurrentDateTime()
    db.run(
      'INSERT INTO mbs (name, content, created_at,zt) VALUES (?, ?, ?,?)',
      [name, str, createdAt, 0],
      function (err) {
        if (err) reject(err)
        else resolve(this.lastID)
      }
    )
  })
}
export function editTemp(db, name, content, id) {
  return new Promise((resolve, reject) => {
    // 更新用户的cookie、店铺名称和登录状态
    db.run('UPDATE mbs SET name=?, content=? WHERE id=?', [name, content, id], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}
export function getTemps(db, { page = 1, pageSize = 10, keyword = '' } = {}) {
  return new Promise((resolve, reject) => {
    // 计算偏移量
    const offset = (page - 1) * pageSize

    // 构建查询条件
    let query = 'SELECT * FROM mbs'
    let countQuery = 'SELECT COUNT(*) AS total FROM mbs'
    const params = []

    if (keyword) {
      query += ' WHERE name LIKE ?'
      countQuery += ' WHERE name LIKE ?'
      const likeKeyword = `%${keyword}%`
      params.push(likeKeyword)
    }

    // 添加分页
    query += ' LIMIT ? OFFSET ?'
    params.push(pageSize, offset)

    // 先查询用户列表
    db.all(query, params, (err, rows) => {
      if (err) {
        reject(err)
        return
      }

      // 再查询总记录数
      db.get(countQuery, keyword ? [`%${keyword}%`] : [], (countErr, countRow) => {
        if (countErr) {
          reject(countErr)
          return
        }

        resolve({
          temps: rows,
          total: countRow.total,
          page,
          pageSize,
          totalPages: Math.ceil(countRow.total / pageSize)
        })
      })
    })
  })
}

// 任务 0 未分配任务 1 已分配任务
export function addTask(db, task, name, shops, mbs) {
  return new Promise((resolve, reject) => {
    const createdAt = getCurrentDateTime()
    db.run(
      'INSERT INTO tasks (task,name,result, created_at,shops,mbs) VALUES (?, ?, ?, ?,?,?)',
      [task, name, 0, createdAt, shops, mbs],
      function (err) {
        if (err) reject(err)
        else resolve(this.lastID)
      }
    )
  })
}
export function getTaskById(db, id) {
  return new Promise((resolve, reject) => {
    db.get('select * from tasks where id = ?', [id], function (err, row) {
      if (err) reject(err)
      else {
        resolve(row || null)
      }
    })
  })
}
export function getTempById(db, id) {
  return new Promise((resolve, reject) => {
    db.get('select * from mbs where id = ?', [id], function (err, row) {
      if (err) reject(err)
      else {
        resolve(row || null)
      }
    })
  })
}
export function editTask(db, name, task, id) {
  return new Promise((resolve, reject) => {
    // 更新用户的cookie、店铺名称和登录状态
    db.run('UPDATE tasks SET name=?, task=? WHERE id=?', [name, task, id], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}
export function getTasks(db, { page = 1, pageSize = 10, keyword = '' } = {}) {
  return new Promise((resolve, reject) => {
    // 计算偏移量
    const offset = (page - 1) * pageSize

    // 构建查询条件
    let query = 'SELECT * FROM tasks'
    let countQuery = 'SELECT COUNT(*) AS total FROM tasks'
    const params = []

    if (keyword) {
      query += ' WHERE name LIKE ?'
      countQuery += ' WHERE name LIKE ?'
      const likeKeyword = `%${keyword}%`
      params.push(likeKeyword)
    }

    // 添加分页
    query += ' LIMIT ? OFFSET ?'
    params.push(pageSize, offset)

    // 先查询用户列表
    db.all(query, params, (err, rows) => {
      if (err) {
        reject(err)
        return
      }

      // 再查询总记录数
      db.get(countQuery, keyword ? [`%${keyword}%`] : [], (countErr, countRow) => {
        if (countErr) {
          reject(countErr)
          return
        }

        resolve({
          tasks: rows,
          total: countRow.total,
          page,
          pageSize,
          totalPages: Math.ceil(countRow.total / pageSize)
        })
      })
    })
  })
}

// 用户任务 zt=0 初始导入 zt=1 已执行
export function addUserTask(db, task_id, good, uid, mb, params, deal_params) {
  return new Promise((resolve, reject) => {
    const createdAt = getCurrentDateTime()
    db.run(
      'INSERT INTO user_task (task_id,good, created_at,zt,result,uid,mb,params,deal_params) VALUES (?, ?, ?,?,?,?,?,?,?)',
      [task_id, good, createdAt, 0, '', uid, mb, params, deal_params],
      function (err) {
        if (err) reject(err)
        else resolve(this.lastID)
      }
    )
  })
}

export function getUserTaskByTaskId(db, task_id, uids) {
  const placeholders = uids.map(() => '?').join(',')
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT * FROM user_task where zt = 0 and result =='' and task_id = ? and uid in (${placeholders})`,
      [task_id, ...uids],
      function (err, rows) {
        if (err) {
          reject(err)
        } else {
          resolve(rows)
        }
      }
    )
  })
}
// 获取已登录且可处理任务的uids列表,去重处理
export function canDoTaskUserIds(db, task_id, uids) {
  console.log('getUserTaskByTaskId:', task_id, uids)
  // 生成占位符 ?, ?, ?, ?...
  const placeholders = uids.map(() => '?').join(',')
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT DISTINCT uid FROM user_task where task_id = ? and uid in (${placeholders})`,
      [task_id, ...uids],
      function (err, rows) {
        if (err) {
          reject(err)
        } else {
          resolve(rows)
        }
      }
    )
  })
}

export function getUserTasks(db, { page = 1, pageSize = 10, userid = '' } = {}) {
  return new Promise((resolve, reject) => {
    // 计算偏移量
    const offset = (page - 1) * pageSize

    // 构建查询条件
    let query = 'SELECT * FROM user_task'
    let countQuery = 'SELECT COUNT(*) AS total FROM user_task'
    const params = []

    if (userid) {
      query += ' WHERE uid = ?'
      countQuery += ' WHERE uid= ?'
      const likeKeyword = `%${userid}%`
      params.push(likeKeyword)
    }

    // 添加分页
    query += ' LIMIT ? OFFSET ?'
    params.push(pageSize, offset)

    // 先查询用户列表
    db.all(query, params, (err, rows) => {
      if (err) {
        reject(err)
        return
      }

      // 再查询总记录数
      db.get(countQuery, userid ? [`%${userid}%`] : [], (countErr, countRow) => {
        if (countErr) {
          reject(countErr)
          return
        }

        resolve({
          tasks: rows,
          total: countRow.total,
          page,
          pageSize,
          totalPages: Math.ceil(countRow.total / pageSize)
        })
      })
    })
  })
}
/**
 * 查询用户任务（带分页）
 * @param {Object} params - 查询参数对象
 * @param {string} [params.uid] - 用户ID (可选)
 * @param {string} [params.task_id] - 任务ID (可选)
 * @param {number} [params.page=1] - 页码，默认为1
 * @param {number} [params.pageSize=10] - 每页条数，默认为10
 */
export function queryUserTaskWithPagination(db, params = {}) {
  const { uid, task_id, page = 1, pageSize = 10 } = params
  console.log('queryUserTaskWithPagination:', params)

  let sql = 'SELECT * FROM user_task'
  const conditions = []
  const values = []
  if (uid) {
    conditions.push('uid = ?')
    values.push(uid)
  }
  if (task_id) {
    conditions.push('task_id = ?')
    values.push(task_id)
  }
  if (conditions.length > 0) {
    sql += ' WHERE ' + conditions.join(' AND ')
  }
  // 添加分页
  const offset = (page - 1) * pageSize
  sql += ` LIMIT ${pageSize} OFFSET ${offset}`
  return new Promise((resolve, reject) => {
    // 先查询总数
    let countSql = 'SELECT COUNT(*) as total FROM user_task'
    if (conditions.length > 0) {
      countSql += ' WHERE ' + conditions.join(' AND ')
    }
    db.all(countSql, values, (err, countRows) => {
      if (err) return reject(err)
      const total = countRows[0].total
      // 查询数据
      console.log('sql:', sql, values)
      db.all(sql, values, (err, rows) => {
        if (err) {
          reject(err)
        } else {
          resolve({
            tasks: rows,
            total: total,
            page,
            pageSize,
            totalPages: Math.ceil(total / pageSize)
          })
        }
      })
    })
  })
}
// 更新任务状态,已经开始执行任务时修改,zt=0 初始导入 zt=1 已执行
export function editUserTaskStatus(db, id, result, resp) {
  return new Promise((resolve, reject) => {
    db.run(
      'UPDATE user_task SET zt = ?, result=?, resp=? WHERE id = ?',
      [1, result, resp, id],
      function (err) {
        if (err) reject(err)
        else resolve(this.changes)
      }
    )
  })
}

export function stopUserTask(db, taskId) {
  return new Promise((resolve, reject) => {
    db.run('update user_task set zt = 2 where task_id = ? and zt = 0', [taskId], function (err) {
      if (err) reject(err)
      else resolve(this.changes)
    })
  })
}

// 保存配置到数据库
export function saveConfig(db, key, value) {
  return new Promise((resolve, reject) => {
    const now = getCurrentDateTime()
    // 先尝试更新，如果没有记录则插入
    db.run(
      'UPDATE configs SET value = ?, updated_at = ? WHERE key = ?',
      [JSON.stringify(value), now, key],
      function (err) {
        if (err) {
          reject(err)
          return
        }

        if (this.changes === 0) {
          // 没有记录，插入新记录
          db.run(
            'INSERT INTO configs (key, value, created_at, updated_at) VALUES (?, ?, ?, ?)',
            [key, JSON.stringify(value), now, now],
            function (err) {
              if (err) reject(err)
              else resolve({ inserted: true, id: this.lastID })
            }
          )
        } else {
          resolve({ updated: true, changes: this.changes })
        }
      }
    )
  })
}

// 从数据库加载配置
export function loadConfig(db, key) {
  return new Promise((resolve, reject) => {
    db.get('SELECT value FROM configs WHERE key = ?', [key], (err, row) => {
      if (err) {
        reject(err)
        return
      }

      if (row) {
        try {
          resolve(JSON.parse(row.value))
        } catch (parseErr) {
          reject(parseErr)
        }
      } else {
        resolve(null) // 配置不存在
      }
    })
  })
}

export function getTaskProcess(db, task_id) {
  return new Promise((resolve, reject) => {
    const sql = `
           select
            (SELECT COUNT(*) from user_task t1 WHERE t1.task_id = ${task_id}) as a,
            (SELECT COUNT(*) from user_task t2 WHERE t2.task_id = ${task_id} and t2.result !='' )as b
        `
    db.get(sql, [], (err, row) => {
      if (err) {
        reject(err)
      } else {
        resolve({
          count1: row.a,
          count2: row.b
        })
      }
    })
  })
}
